﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XH.BMS.Entity;

namespace XH.BMS.DAL
{
    public class LocalDataAccess
    {
        // 创建连接
        SQLiteConnection conn = null;
        // 准备操作对象
        SQLiteCommand comm = null;
        // 数据适配器
        SQLiteDataAdapter adapter = null;
        // 事务管理
        SQLiteTransaction trans = null;

        private void Dispose()
        {
            if (trans != null)
            {
                trans.Dispose();
                trans = null;
            }
            if (adapter != null)
            {
                adapter.Dispose();
                adapter = null;
            }
            if (comm != null)
            {
                comm.Dispose();
                comm = null;
            }
            if (conn != null)
            {
                if (conn.State == System.Data.ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
                conn = null;
            }
        }

        private void Connection()
        {
            if (conn == null)
                conn = new SQLiteConnection(@"data source=D:\\Users\\yhl18\\Desktop\\WPF学习\\00.Project\\01.BMS\\DataBase\\bms.data.db");// Sqlite的连接字符串
                                                                   // 其他数据库连接字符串不一样

            if (conn.State != System.Data.ConnectionState.Open)
                conn.Open();
        }

        private DataTable GetData(string sql)
        {
            try
            {
                this.Connection();
                adapter = new SQLiteDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                adapter.Fill(dt);

                return dt;
            }
            catch (Exception ex) { throw ex; }
            finally
            {
                this.Dispose();
            }
        }

        public DataTable GetBatteryGroup()
        {
            return GetData("select * from b_group");
        }

        public int SaveValues(List<ValueEntity> ves)
        {
            try
            {
                string sql = "insert into \"main\".\"values\"(value_name,value) values(\"{0}\",\"{1}\")";
                this.Connection();
                trans = conn.BeginTransaction();
                int count = 0;
                for (int i = 0; i < ves.Count; i++)
                {
                    comm = new SQLiteCommand(string.Format(sql, ves[i].Name, ves[i].Value), conn);
                    count += comm.ExecuteNonQuery();
                }
                if (count == ves.Count)
                {
                    trans.Commit();
                }
                else
                {
                    trans.Rollback();
                    count = 0;
                }
                return count;
            }
            catch (Exception ex) { throw ex; }
            finally { this.Dispose(); }
        }


        public DataTable GetNormalSettings()
        {
            return GetData("select * from normal_settings");
        }

        public int SaveSettings(List<SettingsEntity> ses)
        {
            try
            {
                string sql = "update normal_settings set p_value_1='{0}',p_value_2='{1}',p_value_3='{2}',p_value_4='{3}' where id = {4}";
                this.Connection();
                trans = conn.BeginTransaction();
                int count = 0;
                for (int i = 0; i < ses.Count; i++)
                {
                    comm = new SQLiteCommand(string.Format(sql, ses[i].PValue1, ses[i].PValue2, ses[i].PValue3, ses[i].PValue4, ses[i].id), conn);
                    count += comm.ExecuteNonQuery();
                }
                if (count == ses.Count)
                {
                    trans.Commit();
                }
                else
                {
                    trans.Rollback();
                    count = 0;
                }
                return count;
            }
            catch (Exception ex) { throw ex; }
            finally { this.Dispose(); }
        }

        public int SaveAlarm(AlarmEntity ae)
        {
            try
            {
                string sql = "insert into alarms(title,desc,date,a_type,name,status) " +
                    "values(\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\")";
                this.Connection();
                comm = new SQLiteCommand(string.Format(sql, ae.Title, ae.Description, ae.Date, ae.AlarmType, ae.Name, 0), conn);
                int count = comm.ExecuteNonQuery();

                return count;
            }
            catch (Exception ex) { throw ex; }
            finally { this.Dispose(); }
        }

        public DataTable GetAlarmsUnchecked()
        {
            string sql = "select * from alarms where status = 0";
            return GetData(sql);
        }
        public DataTable GetAlarms(DateTime start, DateTime end)
        {
            string sql = $"select * from alarms where datetime(date) between " +
                $"datetime('{start.ToString("yyyy-MM-dd HH:mm:ss")}') and datetime('{end.ToString("yyyy-MM-dd HH:mm:ss")}')";

            return GetData(sql);
        }

        public int ChangeStatus(string id)
        {
            try
            {
                string sql = $"update alarms set status=1 where id={id}";
                this.Connection();

                comm = new SQLiteCommand(sql, conn);
                int count = comm.ExecuteNonQuery();

                return count;
            }
            catch (Exception ex) { throw ex; }
            finally { this.Dispose(); }
        }
    }
}
